﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using System.Text;
using System.Data.SqlClient;

namespace KStubs
{
    public class KSConnectionClass : IDisposable
    {
        SqlConnection _connection = null;
        string _connectionstring = null;
        public KSConnectionClass(String ConnectionString)
        {
            _connectionstring = ConnectionString;
        }
        
        internal SqlConnection Connection
        {
            get
            {
                if (_connection != null)
                    return _connection;

                _connection = new SqlConnection(_connectionstring);
                _connection.Open();
                return _connection;
            }

        }


        #region IDisposable Members

        public void Dispose()
        {
            if (_connection == null)
                return;

            try
            {
                _connection.Close();
                _connection = null;
            }
            catch { }
        }

        #endregion
    }

    public class KSQueryClass
    {
        Schema.procedure _procedure;
        KSConnectionClass _connection;
        System.Exception _exception;
        bool _result;
        int _recordcount;
        int _lastRecordID;
        public KSQueryClass(KSConnectionClass Connection, Schema.procedure Procedure)
        {
            _connection = Connection;
            _exception = null;
            _procedure = Procedure;
            _result = false;
            _recordcount = -1;
        }

        public void Run()
        {
            _result = false;
            _exception = null;
            _recordcount = -1;
            _lastRecordID = -1;
            try
            {

                // get a new SqlCommand object
                SqlCommand cmd = new SqlCommand(_procedure.command.name);
                cmd.CommandType = System.Data.CommandType.StoredProcedure; // assume stored procedure
                cmd.Connection = _connection.Connection;
                Schema.result result = new Schema.result();
                result.count = 0;
                _procedure.result = result;
                _lastRecordID = 0;

                // Fill parameters
                foreach (Schema.parameter parm in _procedure.parameters)
                {
                    SqlParameter sqlparm = new SqlParameter();
                    cmd.Parameters.Add(sqlparm);
                    if (parm.outparm)
                        sqlparm.Direction = System.Data.ParameterDirection.Output;

                    sqlparm.ParameterName = parm.name.StartsWith("@") ? parm.name : "@" + parm.name;
                    sqlparm.Value = parm.value; // probably should guard againt sql injection here..
                    
                    #region parameter type
                    switch (parm.type)
                    {
                        case Schema.parameterType.Date:
                            sqlparm.DbType = System.Data.DbType.Date;
                            break;
                        case Schema.parameterType.Integer:
                            sqlparm.DbType = System.Data.DbType.Int32;
                            break;
                        case Schema.parameterType.String:
                            sqlparm.DbType = System.Data.DbType.String;
                            break;
                    }
                    #endregion
                }
 
                // Execute the command
                switch (_procedure.command.action)
                {
                    case Schema.actionType.Delete:
                    case Schema.actionType.Update:
                        _recordcount = cmd.ExecuteNonQuery();
                        result.count = _recordcount;
                        break;
                    case Schema.actionType.Insert:
                        cmd.ExecuteNonQuery();
                        Schema.parameter parm = _procedure.getParameter("InsertID");
                        if (parm != null)
                            _lastRecordID = Convert.ToInt32(cmd.Parameters["@InsertID"].Value);
                        parm.value = _lastRecordID.ToString();

                        _recordcount = (_lastRecordID > 0) ? 1 : 0;
                        result.count = _recordcount;
                        break;
                    case Schema.actionType.Fetch:
                        SqlDataReader reader = cmd.ExecuteReader();
                        result.row = new List<Schema.row>();
                        while (reader.Read())
                        {
                            _recordcount++;
                            result.count = _recordcount;

                            Schema.row row;
                            result.row.Add(row = new Schema.row());

                            #region append attributes to row
                            XmlDocument dummy = new XmlDocument();
                            row.AnyAttr = new List<XmlAttribute>();
                            foreach(Schema.field field in _procedure.fields)
                            {
                                XmlAttribute att = dummy.CreateAttribute(field.name);
                                att.Value = reader[field.name].ToString();
                                row.AnyAttr.Add(att);
                            }
                            #endregion
                
                        }
                        break;
                }
                _result = _recordcount > 0;

            }
            catch(Exception unhandled)
            {
                _exception = unhandled;
            }
        }

        public System.Exception UnhandledException
        {
            get
            {
                return _exception;
            }
        }

        public Boolean Result
        {
            get
            {
                return _result;
            }
        }

        public int RecordsAffected
        {
            get
            {
                return _recordcount;
            }
        }

        public int LastRecordID
        {
            get
            {
                return _lastRecordID;
            }
        }


    }

}
